5.7 Database Transaction Handling

1. Db.tx Transactions

The Db utility class provides a series of tx(...) methods to support database transactions. Here's an example using Java 8's lambda syntax:

Db.tx(() -> {
  Db.update("update t1 set f1 = ?", 123);
  Db.update("update t2 set f2 = ?", 456);
  return true;
});

In the code above, both Db.update operations initiate a transaction. Returning true will commit the transaction, while returning false will roll back. The advantage of using Db.tx(...) is that it offers finer control over transactions. It allows rollbacks without the need for exceptions.

The Db.tx method is for the primary data source by default. For transactions on other data sources, use Db.use(configName).tx(...). Additionally, Db.tx(...) supports specifying transaction levels:

Db.tx(Connection.TRANSACTION_SERIALIZABLE, () -> {
  Db.update(...);
  new User().setNickName("james").save();
  return true;
});

Note: MySQL tables must be set to the InnoDB engine for transaction support. MyISAM does not support transactions.

2. Declarative Transactions

ActiveRecord supports declarative transactions, implemented using interceptors provided by the ActiveRecordPlugin. Here's an example:

@Before(Tx.class)
public void trans_demo() {
  Integer transAmount = getInt("transAmount");
  Integer fromAccountId = getInt("fromAccountId");
  Integer toAccountId = getInt("toAccountId");
  
  Db.update("update account set cash = cash - ? where id = ?", transAmount, fromAccountId);
  Db.update("update account set cash = cash + ? where id = ?", transAmount, toAccountId);
}

Note that just by declaring the Tx interceptor, the action method supports transactions.

If you want to use a try-catch block with the Tx interceptor configured at the Controller layer for response control, use the following approach:

@Before(Tx.class)
public void trans() {
  try {
    service.justDoIt(...);
    render("ok.html");
  } catch (Exception e) {
    render("error.html");
    throw e;
  }
}

This approach ensures that any exceptions are caught and the appropriate response template is rendered. However, always rethrow the exception to ensure the Tx interceptor is aware and can roll back the transaction.

For specific declarative transactions, ActiveRecord provides TxByActionKeys, TxByActionKeyRegex, TxByMethods, and TxByMethodRegex. Example:

public void configInterceptor(Interceptors me) {
   me.add(new TxByMethodRegex("(.*save.*|.*update.*)"));
   me.add(new TxByMethods("save", "update"));
   me.add(new TxByActionKeyRegex("/trans.*"));
   me.add(new TxByActionKeys("/tx/save", "/tx/update"));
}

If you want to roll back transactions for non-primary data sources, use annotations:

@TxConfig("otherConfigName")
@Before(Tx.class)
public void doIt() {
   ...
}

3. Tips & Tricks

It's recommended to use Db.tx(...) for database transactions for several reasons:

1. It allows for the finest granularity, ensuring optimal performance.
2. It lets you use return values to control whether or not to roll back a transaction. The Tx interceptor, on the other hand, relies on catching exceptions.
3. The Java 8 lambda syntax makes the code concise.

When using the Tx interceptor, it's recommended to have an additional ExceptionInterceptor to handle exceptions thrown by Tx.

In summary, always prioritize using Db.tx(...) for transaction handling.

4. Transaction Levels & Performance

JDBC's default transaction level is Connection.TRANSACTION_READ_COMMITTED. However, to cater to specific application scenarios, JFinal's ActiveRecordPlugin uses Connection.TRANSACTION_REPEATABLE_READ by default. Adjusting the transaction level can be crucial for performance, especially in cases of high concurrent lock contention. Configuring the transaction level can help:

public void configPlugin(Plugins me) {
    ActiveRecordPlugin arp = new ActiveRecordPlugin(...);
    arp.setTransactionLevel(Connection.TRANSACTION_REPEATABLE_READ);
    me.add(arp);
}

Remember, the appropriate transaction level can significantly impact your application's performance. Always choose wisely based on your specific needs.